﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Common;
using System.Reflection;

namespace mluDataEntity
{
    public class Join
    {
        public DataTable ClientiDepositati()
        {
            using (mluEntities context = new mluEntities())
            {
                try
                {
                    var join = (from contextClienti in context.clienti.AsEnumerable()
                                                 join contextDepositati in context.depositati.AsEnumerable()
                                                    on contextClienti.ID equals contextDepositati.IdCliente
                                                 select new
                                                 {
                                                     contextClienti,
                                                     contextDepositati
                                                 });

                    System.Reflection.PropertyInfo[] propInfo = null;
                    DataTable dt = new DataTable();
                    var first = join.First();
                    propInfo = first.GetType().GetProperties();
                    clienti c = new clienti();
                    System.Reflection.PropertyInfo[] propClienti = c.GetType().GetProperties();
                    depositati d = new depositati();
                    System.Reflection.PropertyInfo[] propDepositati = d.GetType().GetProperties();
                    for (int i = 0; i < propInfo.Length; i++)
                    {
                        switch (propInfo[i].Name)
                        {
                            case "contextClienti":
                                
                                
                                for (int j = 0; j < propClienti.Length; j++)
                                {
                                    dt.Columns.Add(propClienti[j].Name, propClienti[j].PropertyType);
                                }
                                break;
                            case "contextDepositati":
                                
                                for (int j = 0; j < propDepositati.Length; j++)
                                {
                                    if (!propDepositati[j].PropertyType.FullName.Contains("DateTime"))
                                        dt.Columns.Add(propDepositati[j].Name, propDepositati[j].PropertyType);
                                    else
                                        dt.Columns.Add(propDepositati[j].Name, typeof(DateTime));
                                }
                                break;
                        }
                    }

                    foreach (var index in join)
                    {
                        DataRow dr = dt.NewRow();
                        foreach (System.Reflection.PropertyInfo prop in propInfo)
                        {
                            switch (prop.Name)
                            {
                                case "contextClienti":
                                    c = index.contextClienti;
                                    foreach (System.Reflection.PropertyInfo propCliente in propClienti)
                                    {
                                        dr[propCliente.Name] = propCliente.GetValue(c);
                                    }
                                    break;
                                case "contextDepositati":
                                    d = index.contextDepositati;
                                    foreach (System.Reflection.PropertyInfo propDepositato in propDepositati)
                                    {
                                        dr[propDepositato.Name] = propDepositato.GetValue(d);
                                    }
                                    break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                    
                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        
        public DataTable ClientiDepositatiLibri()
        {
            using (mluEntities context = new mluEntities())
            {
                try
                {
                    var join = (from contextClienti in context.clienti.AsEnumerable()
                                join contextDepositati in context.depositati.AsEnumerable()
                                        on contextClienti.ID equals contextDepositati.IdCliente
                                join contextLibri in context.libri
                                        on contextDepositati.IdLibro equals contextLibri.IDlibro
                                select new
                                {
                                    contextClienti,
                                    contextDepositati,
                                    contextLibri
                                });

                    System.Reflection.PropertyInfo[] propInfo = null;
                    DataTable dt = new DataTable();
                    var first = join.First();
                    propInfo = first.GetType().GetProperties();
                    
                    System.Reflection.PropertyInfo[] propClienti = first.contextClienti.GetType().GetProperties();
                    System.Reflection.PropertyInfo[] propDepositati = first.contextDepositati.GetType().GetProperties();
                    System.Reflection.PropertyInfo[] propLibri = first.contextLibri.GetType().GetProperties();

                    //for (int i = 0; i < propInfo.Length; i++)

                    foreach (PropertyInfo info in propInfo)
                    {
                        switch (info.Name)
                        {
                            case "contextClienti":
                                foreach (PropertyInfo propCliente in propClienti)
                                {
                                    dt.Columns.Add(propCliente.Name,propCliente.PropertyType);
                                }
                                break;
                            case "contextDepositati":
                                foreach(PropertyInfo propDepositato in propDepositati)
                                {
                                    if (!propDepositato.PropertyType.FullName.Contains("DateTime"))
                                        dt.Columns.Add(propDepositato.Name,propDepositato.PropertyType);
                                    else
                                        dt.Columns.Add(propDepositato.Name,typeof(DateTime));
                                }
                                break;
                            case "contextLibri":
                                foreach (PropertyInfo propLibro in propLibri)
                                {
                                    if (!propLibro.PropertyType.FullName.Contains("DateTime") && !propLibro.PropertyType.FullName.Contains("Single"))
                                        dt.Columns.Add(propLibro.Name, propLibro.PropertyType);
                                    else if (propLibro.PropertyType.FullName.Contains("DateTime"))
                                        dt.Columns.Add(propLibro.Name, typeof(DateTime));
                                    else if (propLibro.PropertyType.FullName.Contains("Single"))
                                        dt.Columns.Add(propLibro.Name, typeof(Single));
                                }
                                break;
                        }
                    }

                    clienti c;
                    depositati d;
                    libri l;
                    foreach (var index in join)
                    {
                        DataRow dr = dt.NewRow();
                        foreach (System.Reflection.PropertyInfo prop in propInfo)
                        {
                            switch (prop.Name)
                            {
                                case "contextClienti":
                                    c = index.contextClienti;
                                    foreach (System.Reflection.PropertyInfo propCliente in propClienti)
                                    {
                                        dr[propCliente.Name] = propCliente.GetValue(c);
                                    }
                                    break;
                                case "contextDepositati":
                                    d = index.contextDepositati;
                                    foreach (System.Reflection.PropertyInfo propDepositato in propDepositati)
                                    {
                                        dr[propDepositato.Name] = propDepositato.GetValue(d);
                                    }
                                    break;
                                case "contextLibri":
                                    l = index.contextLibri;
                                    foreach (PropertyInfo propLibro in propLibri)
                                    {
                                        if (string.Compare(propLibro.Name.ToString(), "prezzoori", true) == 0)
                                        {
                                            if (l.prezzoOri == null)
                                                dr[propLibro.Name] = DBNull.Value;
                                            else
                                                dr[propLibro.Name] = l.prezzoOri;
                                        }
                                        else
                                        {
                                            dr[propLibro.Name] = propLibro.GetValue(l);
                                        }

                                    }
                                    break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }

                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public DataTable ClientLibri()
        {
            using (mluEntities context = new mluEntities())
            {
                try
                {
                    var join = (from contextClienti in context.clienti.AsEnumerable()
                                join contextDepositati in context.depositati.AsEnumerable()
                                        on contextClienti.ID equals contextDepositati.IdCliente
                                join contextLibri in context.libri
                                        on contextDepositati.IdLibro equals contextLibri.IDlibro
                                select new
                                {
                                    contextClienti.Nome,
                                    contextClienti.Cognome,
                                    contextLibri.titolo,
                                    contextDepositati.prezzo,
                                    contextDepositati.data,
                                    contextDepositati.datares
                                });

                    System.Reflection.PropertyInfo[] propInfo = null;
                    DataTable dt = new DataTable();
                    var first = join.First();
                    propInfo = first.GetType().GetProperties();


                    foreach (PropertyInfo info in propInfo)
                    {
                        if (!info.PropertyType.FullName.Contains("DateTime"))
                            dt.Columns.Add(info.Name, info.PropertyType);
                        else
                            dt.Columns.Add(info.Name, typeof(DateTime));
                    }

                    foreach (var index in join)
                    {
                        DataRow dr = dt.NewRow();
                        foreach (System.Reflection.PropertyInfo prop in propInfo)
                        {
                            dr[prop.Name] = prop.GetValue(index);
                        }
                        dt.Rows.Add(dr);
                    }

                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        
    }
}
